import pymysql
import pymysql.cursors
from rest_framework.response import Response
import redis
from django.conf import settings as stg


SQL = {
    'res':"SELECT * FROM `tb_custom_info` WHERE (c_name LIKE '%{0}%' OR c_name_short LIKE '%{0}%' OR c_type LIKE '%{0}%') AND (c_name LIKE '%{1}%' OR c_name_short LIKE '%{1}%' OR c_type LIKE '%{1}%')\
           AND (c_name LIKE '%{2}%' OR c_name_short LIKE '%{2}%' OR c_type LIKE '%{2}%')"

}

class Database:
    def __init__(self,host,port,user,password,dbname):
        self.host = host
        self.port = port
        self.user = user
        self.passwd = password
        self.db = dbname
        self.dbtype = 'mysql'
        self.conn = None
        self.cur = None
        self.charset = 'utf8'

    def get_conn(self):
        if self.dbtype == 'mysql':
            return pymysql.connect(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
        else:
            return False

    def get_cursor(self):
        try:
            self.conn = self.get_conn()
            self.cur = self.conn.cursor()
            return True
        except Exception as err:
            #print('db %s failed,Message:%s' % (self.dbtype, err))
            return False

    def close(self):
        self.cur.close()
        self.conn.close()

    def select_table(self,sql):
        try:
            self.cur.execute(sql)
            rows = self.cur.fetchall()
            return rows
        except Exception as err:
            #print('Message:%s' % (err))
            return  False

    def insert_table(self, sql):
        try:
            self.cur.execute(sql)
            a = self.conn.commit()
            return a
        except Exception as err:
            #print('Message:%s' % err)
            return  False

    def delect(self,sql):
        try:
            self.cur.execute(sql)
            self.conn.commit()
            return True
        except Exception as e:
            self.conn.rollback()

def conn_ims(sql):
    db = Database(host=stg.DATABASES['db1']['HOST'], port=stg.DATABASES['db1']['PORT'],
                  user=stg.DATABASES['db1']['USER'],
                  password=stg.DATABASES['db1']['PASSWORD'],
                  dbname=stg.DATABASES['db1']['NAME'])
    db.get_conn()
    db.get_cursor()
    data = db.select_table(sql)
    db.close()
    return data


def my_select1(sql):
    db = Database(host=stg.DATABASES['db1']['HOST'], port=stg.DATABASES['db1']['PORT'],
                  user=stg.DATABASES['db1']['USER'],
                  password=stg.DATABASES['db1']['PASSWORD'],
                  dbname=stg.DATABASES['db1']['NAME'])
    db.get_conn()
    db.get_cursor()
    data = db.select_table(sql)
    db.close()
    return data


def delect_data(sql):
    db = Database(host=stg.DATABASES['db1']['HOST'], port=stg.DATABASES['db1']['PORT'],
                  user=stg.DATABASES['db1']['USER'],
                  password=stg.DATABASES['db1']['PASSWORD'],
                  dbname=stg.DATABASES['db1']['NAME'])
    db.get_conn()
    db.get_cursor()
    db.delect(sql)
    db.close()


def insert_data(sql):
    db = Database(host=stg.DATABASES['db1']['HOST'], port=stg.DATABASES['db1']['PORT'],
                  user=stg.DATABASES['db1']['USER'],
                  password=stg.DATABASES['db1']['PASSWORD'],
                  dbname=stg.DATABASES['db1']['NAME'])
    db.get_conn()
    db.get_cursor()
    db.insert_table(sql)
    db.close()


red = redis.Redis(host=stg.REDIS_HOST, port=stg.REDIS_PORT,
                  db=6, password=stg.REDIS_PASSWORD)


def red_conn(key):
    try:
        if red.exists(key):
            red_data = red.get(key)
            return red_data.decode('utf-8')
        else:
            return None
    except Exception as e:
        return None


def get_keys(key):
    try:
        keys = red.keys(key)
        return keys
    except Exception as e:
        return []
